Working with JSON Data Using JSON_EXTRACT() and JSON_CONTAINS()
MySQL provides native JSON functions to query and manipulate JSON data stored in JSON columns. Two commonly used functions are JSON_EXTRACT() and JSON_CONTAINS().
Retrieves data from a JSON document using a JSON path expression.
Returns the value at the specified path as JSON.
Can extract nested values, arrays, or objects.
Checks whether a specific JSON value exists within a JSON document.
Returns 1 if the value exists, 0 otherwise.
Supports optional path argument to search within nested structures.
JSON_EXTRACT() is read-only and ideal for retrieving values from JSON columns.
JSON_CONTAINS() is useful in WHERE clauses to filter rows containing specific JSON data.
Both functions can work with indexes on generated columns to improve performance.
JSON paths use the
$symbol as the root and dot notation or array indices to traverse the structure.
In summary: Use JSON_EXTRACT() to retrieve specific values from JSON documents, and JSON_CONTAINS() to test whether certain values exist in JSON columns, enabling powerful filtering and querying of semi-structured data in MySQL.